﻿IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[cms_sp_MediaFileSelectOne]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[cms_sp_MediaFileSelectOne]
GO

create procedure [dbo].[cms_sp_MediaFileSelectOne]
(
@SiteId int = null,
@SiteGroupId int = null,
@MediaFileId int
)
as

select 
top 1
	MediaFiles.MediaFileId,
	MediaFiles.SiteId,
	MediaFiles.SiteGroupId,
	MediaFiles.MimeTypeId,
	MimeTypes.MimeTypeName,
	MimeTypes.ContentType,
	MimeTypes.IsImage,
	MediaFiles.OriginalFileName,
	MediaFiles.LinkedFileName,
	MediaFiles.Height,
	MediaFiles.Width,
	MediaFiles.FileSize,
	MediaFiles.CreatedBy,
	MediaFiles.DateCreated,
	MediaFiles.LastUpdatedBy,
	MediaFiles.DateLastUpdated,
	mc.BlockFromSearchEngines
from dbo.MediaFiles
left join dbo.MimeTypes
	on MimeTypes.MimeTypeId = MediaFiles.MimeTypeId
left join MediaContentFileLinks mfl on mfl.MediaFileId = MediaFiles.MediaFileId
left join MediaContents mc on mc.MediaContentId = mfl.MediaContentId
where ((MediaFiles.SiteGroupId is null and MediaFiles.SiteId is null) or 
	(MediaFiles.SiteGroupId = @SiteGroupId and MediaFiles.SiteId is null) or
	(MediaFiles.SiteGroupId = @SiteGroupId and MediaFiles.SiteId = @SiteId)) and
	MediaFiles.MediaFileId = @MediaFileId

GO


